﻿using Corina.Datamodel.Entities;
using Corina.Enumerations;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Corina.DataContext.Service
{
    public class CityService : ServiceBase
    {
        public DataTable GetAllItems(string connectionString, string provinceCode, string key, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY tc.Code) AS ROW_NUMBER,tc.*,tp.Name AS Province ");
            builder.Append("FROM t_city as tc ");
            builder.Append("LEFT JOIN t_province AS tp ON tp.Code = LEFT(tc.Code,2) ");
            builder.Append("WHERE 1=1 ");
            if (!string.IsNullOrEmpty(provinceCode))
            {
                builder.Append("AND tp.Code = @Code ");
                paras.Add(new SqlParameter("@Code", provinceCode));
            }
            builder.Append("AND (tc.Name LIKE @Key OR tc.English LIKE @Key OR tc.PostCode LIKE @Key OR tc.AreaCode LIKE @Key) ");
            paras.Add(new SqlParameter("@Key", string.Format("%{0}%", key)));

            builder.Append(") AS query ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;
        }

        public List<CityInfo> GetAllItems(string connectionString,string provinceCode)
        {
            string queryString = "SELECT * FROM t_city AS tc WHERE LEFT(tc.Code,2) = @Code ";
            return DbOperator.GetEntities<CityInfo>(connectionString, queryString, new SqlParameter("@Code", provinceCode));
        }

        public bool Update(string connectionString, CityInfo entity, EntityOperation operation)
        {
            return DbOperator.UpdateEntity(connectionString, entity, operation);
        }

        public string GetCode(string connectionString,string provinceCode)
        {
            string queryString = "SELECT MAX(Convert(int, tc.Code)) FROM t_city AS tc WHERE LEFT(tc.Code,2) = @Province";
            DataTable table = DbOperator.GetDataTable(connectionString, queryString, new SqlParameter("@Province", provinceCode));
            if (table == null || table.Rows.Count == 0 || string.IsNullOrEmpty(table.Rows[0][0].ToString()))
            {
                return provinceCode + "01";
            }
            else
            {
                return (Convert.ToInt32(table.Rows[0][0]) + 1).ToString().PadLeft(4, '0');
            }
        }

        public CityInfo GetCityInFo(string connectionString, int id)
        {
            return DbOperator.GetEntity<CityInfo>(connectionString, new List<string>() { "Id" }, new List<object>() { id });
        }
    }
}
